﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TalentPools.Model;
namespace TalentPools.DAL
{
    public class checkresumeService
    {
        #region 根据ID获得自己的简历数目
        /// <summary>
        /// 根据ID获得自己的简历数目
        /// </summary>
        /// <param name="name">用户名</param>
        /// <returns></returns>
        public int getcountByname(string username)
        {
            string sql = "select count(*) from resume where username=@username";
            SqlParameter[] par = { new SqlParameter("@username", username) };

            return (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, sql, par);
        }
        #endregion
        #region 根据ID查找自己的简历的详细信息
        /// <summary>
        /// 根据ID查找自己的简历的详细信息
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <returns></returns>
        public List<resume> GetResumeByUserName(string username)
        {
            List<resume> list = new List<resume>();
            string sql = "select * from resume where username=@username";
            SqlParameter[] par = { new SqlParameter("@username", username) };
            SqlDataReader read = DBHelper.ExecuteGetReader(CommandType.Text, sql, par);
            while (read.Read())
            {
                resume re = new resume();
                re.Id = Convert.ToInt32(read["id"]);
                re.username = read["username"].ToString();
                re.name = read["name"].ToString();
                re.sex = read["sex"].ToString();
                re.birthday = Convert.ToDateTime(read["birthday"]);
                re.marriage = read["marriage"].ToString();
                re.card = read["card"].ToString();
                re.politicsStatus = read["politicsStatus"].ToString();
                re.HKPlace = read["HKPlace"].ToString();
                re.city = read["city"].ToString();
                re.currentAddress = read["currentAddress"].ToString();
                re.zipCode = read["zipCode"].ToString();
                re.cellphone = read["cellphone"].ToString();
                re.phoneCode = read["phoneCode"].ToString();
                re.email = read["email"].ToString();
                re.homePage = read["homePage"].ToString();
                re.isPublic = Convert.ToBoolean(read["isPublic"]);
                re.workYear = read["workYear"].ToString();
                re.resumeName = read["resumeName"].ToString();
                re.lastUpdateTime = Convert.ToDateTime(read["lastUpdateTime"]);
                list.Add(re);
            }
            return list;

        }
        #endregion
        #region 根据Id删除自己的简历
        /// <summary>
        /// 根据Id删除自己的简历
        /// </summary>
        /// <param name="id">简历ID</param>
        /// <returns></returns>
        public bool deletResumeByid(int id)
        {
            string sql = "delete from  resume where id=@id";
            SqlParameter[] par = { new SqlParameter("@id", id) };
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, par);
            if (i > 0)
            {
                return true;
            }
            return false;

        }
        #endregion
        #region 根据简历ID查找自己的简历的详细信息
        /// <summary>
        /// 根据ID查找自己的简历的详细信息
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <returns></returns>
        public List<resume> getresumeByresumeid(int id)
        {
            List<resume> list = new List<resume>();
            string sql = "select resume.* from resume where Id=@resumeId";
            SqlParameter par = new SqlParameter("@resumeId", id);
            using (SqlDataReader read = DBHelper.ExecuteGetReader(CommandType.Text, sql, par))
            {
                while (read.Read())
                {
                    resume re = new resume();
                    re.username = read["username"].ToString();
                    re.name = read["name"].ToString();
                    re.sex = read["sex"].ToString();
                    re.birthday = Convert.ToDateTime(read["birthday"]);
                    re.marriage = read["marriage"].ToString();
                    re.card = read["card"].ToString();
                    re.politicsStatus = read["politicsStatus"].ToString();
                    re.HKPlace = read["HKPlace"].ToString();
                    re.city = read["city"].ToString();
                    re.currentAddress = read["currentAddress"].ToString();
                    re.zipCode = read["zipCode"].ToString();
                    re.cellphone = read["cellphone"].ToString();
                    re.phoneCode = read["phoneCode"].ToString();
                    re.email = read["email"].ToString();
                    re.homePage = read["homePage"].ToString();
                    re.resumeName = read["resumeName"].ToString();
                    re.isPublic = Convert.ToBoolean(read["isPublic"]);
                    re.lastUpdateTime = Convert.ToDateTime(read["lastUpdateTime"]);
                    re.workYear = read["workYear"].ToString();
                    list.Add(re);
                }
            }
            return list;

        }

        public DataSet GetResumeByResumeId(int resumeId)
        {
            string sql = "select " + "resume.name,resume.birthday,card,HKPlace,city,currentAddress,zipCode,cellphone,phoneCode,email,A.value,B.value,C.value from resume," +
  "(select value from baseConfig where [key]=(select marriage from resume where Id=@resumeId))as A," +
  "(select value from baseConfig where [key]=(select politicsStatus from resume where Id=@resumeId))as B," +
  "(select value from baseConfig where [key]=(select workYear from resume where Id=@resumeId)) as C," +
  "(select value from baseConfig where [key]=(select workYear from resume where Id=@resumeId)) as D" +
  "where Id=@resumeId";
            SqlParameter para = new SqlParameter("@resumeId", resumeId);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, para);
        }
        #endregion

        #region 获取分页简历的数据
        public DataSet GetResumePage(int pageIndex, int pageSize, string where)
        {
            string sql = "select * from(select ROW_NUMBER() over(order by Id)RowIndex,* from resume where username like '%'+@where+'%')a where RowIndex between @begin and @end";
            SqlParameter[] paras = new SqlParameter[] {
            new SqlParameter("@begin",pageSize*(pageIndex-1)+1),
            new SqlParameter("@end",pageIndex*pageSize),
            new SqlParameter("@where",where)
            };
            using (DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql, paras))
            {
                return ds;
            }
        }
        #endregion

        /// <summary>
        /// 根据条件查询记录数
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public int GetResumeCount(string where)
        {
            string sql = "select count(*) from resume where username like '%'+@where+'%'";
            SqlParameter para = new SqlParameter("@where", where);
            return (int)DBHelper.ExecuteScalar(CommandType.Text, sql, para);
        }
    }
}
